package sqlServer;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;

import javaUtils.DateUtils;

import javax.crypto.*;
import javax.crypto.spec.*;
import javax.swing.JOptionPane;
import javax.swing.JTree;
import javax.swing.text.Position;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.DefaultTreeModel;
import javax.swing.tree.MutableTreeNode;
import javax.swing.tree.TreePath;
import javax.xml.namespace.QName;
import javax.xml.soap.MessageFactory;
import javax.xml.soap.SOAPBody;
import javax.xml.soap.SOAPBodyElement;
import javax.xml.soap.SOAPConnection;
import javax.xml.soap.SOAPConnectionFactory;
import javax.xml.soap.SOAPElement;
import javax.xml.soap.SOAPHeader;
import javax.xml.soap.SOAPMessage;

import com.mysql.jdbc.Statement;

import windowUnits.PrimaryWeb;
import xml.WebPage;
import xml.XmlWebSet;



public class AppConnection {
	
	public final static int MYSQL_DB = 0;
	private Connection conn = null;
	private boolean isConnected = false;
	private XmlWebSet xmSet;
	private String editedWebPage;
	private int dbType;
	private HashMap <Integer, Menus> menusTable = new HashMap <Integer, Menus>();
	
	public AppConnection(XmlWebSet xmSet,String editedWebPage, int dbType) {
		this.xmSet = xmSet;
		this.editedWebPage = editedWebPage;
		this.dbType = dbType;
		try {
            switch(dbType){
            case MYSQL_DB: Class.forName("com.mysql.jdbc.Driver").newInstance();
            }
        } catch (Exception ex) {
        	System.out.println("Exception: " + ex.getMessage());
        }
	}
	
	public void createConnection() {
		try {
			System.out.println("Connection for: "+editedWebPage);
			System.out.println("link for: jdbc:mysql://"+xmSet.getWebPageParameter(editedWebPage, "dbLink")+"/"
						+xmSet.getWebPageParameter(editedWebPage, "dbName")+"?" 
   						+"user="+xmSet.getWebPageParameter(editedWebPage, "dbUser")
   						+"&password="+xmSet.getWebPageParameter(editedWebPage, "dbPsw"));
			
			if(xmSet.getWebPageParameter(editedWebPage, "dbLink") != ""){
			   switch(dbType){
			   case MYSQL_DB: conn = 
				   			   DriverManager.getConnection("jdbc:mysql://"+xmSet.getWebPageParameter(editedWebPage, "dbLink")+"/"
		    		   						+xmSet.getWebPageParameter(editedWebPage, "dbName")+"?" 
		    		   						+"user="+xmSet.getWebPageParameter(editedWebPage, "dbUser")
		    		   						+"&password="+xmSet.getWebPageParameter(editedWebPage, "dbPsw")
		    		   						);
			   }
			}else{
				throw new Exception("DB Link Error");
			}
		    isConnected = true;	
		    System.out.println("is Connected is");
		} catch (Exception exl){
			System.out.println("Exception: " + exl.getMessage());
			JOptionPane.showMessageDialog(null,exl.getMessage());
			isConnected = false;
		}
	}
	
	public void closeConnection(){
		try {
			conn.close();
			isConnected = false;
			System.out.println("DisConnected");
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("VendorError: " + e.getErrorCode());
		}
	}
	
	public boolean isConnected(){
		return isConnected;
	}
	
	
	public static String asHex (byte buf[]) {
	
		StringBuffer strbuf = new StringBuffer(buf.length * 2);
		
		int i;
		
		for (i = 0; i < buf.length; i++) {
		
			if (((int) buf[i] & 0xff) < 0x10)
			
			strbuf.append("0");
			
			strbuf.append(Long.toString((int) buf[i] & 0xff, 16));
		
		}
		
		return strbuf.toString();
	
	} 
	
	public void setWebPagesStructure(JTree tree, HashMap <Integer, InstalledMenus> installedMenusMap) {
		Statement stMenu = null;
		
		try{
			stMenu = (Statement) conn.createStatement();
			ResultSet rs = stMenu.executeQuery(getSqlQuery(SELECT_INSTALLED_MENU_SETUP));
			int i = 1;
			while (rs.next()) {
				InstalledMenus instMenus = new InstalledMenus();
				instMenus.setMenu(InstalledMenus.MENU_ID, rs.getInt("ID"));
				instMenus.setMenu(InstalledMenus.MENU_POSITION, rs.getString("menu_position"));
				instMenus.setMenu(InstalledMenus.MAX_SUBCATEGORIES, rs.getInt("max_subcategories"));
				instMenus.setMenu(InstalledMenus.MENU_FOLDER, rs.getString("menu_folder"));
				instMenus.setMenu(InstalledMenus.MENU_NAME, rs.getString("menu_name"));
				instMenus.setMenu(InstalledMenus.MENU_DESCRIBE, rs.getString("menu_describe"));
				instMenus.setMenu(InstalledMenus.MENU_PICTUREEXAMPLE, rs.getString("menu_picture_example"));
				System.out.println(rs.getString("menu_name")+"/"+rs.getString("menu_position"));
				installedMenusMap.put(i, instMenus);
			    i++;
			}
			
			initMenusHashMap();
			
			DefaultTreeModel model = (DefaultTreeModel)tree.getModel();
			tree.removeAll();
			//TreePath path = tree.getNextMatch("Web", 0, Position.Bias.Forward);
			model.setRoot(getTreeHierarchy(0, "index",0));
			
			
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,e.getMessage());
			e.printStackTrace();
		}finally{
		  try {
			stMenu.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}

		
	}
	
	private DefaultMutableTreeNode getTreeHierarchy(int objectId, String objectType, int order){
		PreparedStatement st = null;
		ResultSet rs = null;
		DefaultMutableTreeNode node = null;
		try{
			node = new DefaultMutableTreeNode();
			Object sqlObject = getUserObjectToNode(objectId,objectType);
			((BaseDataObject)sqlObject).setObjectOrder(order);
			node.setUserObject(sqlObject);
			st = (PreparedStatement) conn.prepareStatement(getSqlQuery(SELECT_OBJECT_REFERENCE));
			st.setInt(1, objectId);
			st.setString(2, objectType);
			
			rs = st.executeQuery();
			
			while (rs.next()) {
				node.add(getTreeHierarchy(rs.getInt("object_child_id"), rs.getString("object_child_type"),rs.getInt("object_order")));
			}
			
			
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,e.getMessage());
			e.printStackTrace();
		}finally{
			try {
			st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return node;
	}
	
	private Object getUserObjectToNode(int objectId, String objectType){
		Object userObject = null;
		if(objectType.equals("index")){
			userObject = new PrimaryWeb(editedWebPage);
		}else if(objectType.equals("menu")){
			userObject = getMenu(objectId);
		}
			
		return userObject;
	}
	
	private void initMenusHashMap(){
		Statement stMenu = null;
		ResultSet rs = null;
		try{
			stMenu = (Statement) conn.createStatement();
			rs = stMenu.executeQuery(getSqlQuery(SELECT_MENUS));
			while (rs.next()) {
				Menus instMenus = new Menus();
				instMenus.setMenu(Menus.MENUS_ID, rs.getInt("ID"));
				instMenus.setMenu(Menus.MENU_TYPE_ID, rs.getInt("menu_type_id"));
				String publichedFrom = rs.getString("publiched_from");
				if(publichedFrom != null)  publichedFrom = DateUtils.getDateFrom(DateUtils.df.parse(publichedFrom));
				instMenus.setMenu(Menus.PUBLICHED_FROM, publichedFrom);
				String publichedTo = rs.getString("publiched_to");
				if(publichedTo != null)  publichedTo = DateUtils.getDateFrom(DateUtils.df.parse(publichedTo));
				instMenus.setMenu(Menus.PUBLICHED_TO, publichedTo);
				instMenus.setMenu(Menus.PUBLICHED, rs.getInt("publiched"));
				instMenus.setMenu(Menus.MENU_POSITION, rs.getString("menu_position"));
				instMenus.setMenu(Menus.MENU_NAME, rs.getString("menu_name"));
				instMenus.setMenu(Menus.MAX_SUBCATEGORIES, rs.getString("max_subcategories"));
				menusTable.put(Integer.parseInt(instMenus.getMenuItem(Menus.MENUS_ID)), instMenus);
			}
			
			
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,e.getMessage());
			e.printStackTrace();
		}finally{
			try {
			stMenu.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	private Menus getMenu(int menuId){
		return menusTable.get(menuId);	
	}
	
	private final static int SELECT_INSTALLED_MENU_SETUP = 1;
	private final static int SELECT_MENUS = 2;
	private final static int SELECT_OBJECT_REFERENCE = 3;
	private final static int UPDATE_ITEM_ORDER = 4;
	private final static int UPDATE_MENU_ITEM = 5;
	private final static int INSERT_MENU_ITEM = 6;
	private final static int INSERT_OBJECT_REFERENCE = 7;
	private final static int SELECT_OBJECT_REFERENCE_ITEM_COUNT = 8;
	private final static int DELETE_OBJECT_FROM_REFERENCE = 9;
	
	private String getSqlQuery(int query){
		String selectQuery = null;
		switch(query){
		case SELECT_INSTALLED_MENU_SETUP: 
			selectQuery = "SELECT * FROM scms_instaled_menus";
			break;
		case SELECT_MENUS:
			selectQuery = "SELECT m.*,im.menu_position,im.max_subcategories FROM scms_menu m, scms_instaled_menus im " +
					      "WHERE im.ID = m.menu_type_id";
			break;
		case SELECT_OBJECT_REFERENCE:
			selectQuery = "SELECT * FROM scms_objects_referencer WHERE object_id = ? AND object_type = ?";
			break;
		case SELECT_OBJECT_REFERENCE_ITEM_COUNT:
			selectQuery = "SELECT count(*) items_count FROM scms_objects_referencer " +
						  "WHERE OBJECT_ID = ? AND OBJECT_TYPE = ?";
			break;			
		case UPDATE_ITEM_ORDER:
			selectQuery = "UPDATE scms_objects_referencer "+ 
						  "SET object_order = ? WHERE object_id = ? " +
						  "AND object_type = ? " +
						  "AND object_child_id = ? " +
						  "AND object_child_type = ?";
			break;
		case UPDATE_MENU_ITEM:
			selectQuery = "UPDATE scms_menu SET " +
						  "menu_name = ?, " +
						  "menu_type_id = ?, " +
						  "publiched_from = ?, " +
						  "publiched_to = ? " +
						  "WHERE ID = ?";
			break;
		case INSERT_MENU_ITEM:
			selectQuery = "INSERT INTO scms_menu (ID, menu_name, menu_type_id, publiched_from, publiched_to, publiched) " +
		       			  "VALUES(0,?,?,?,?,?)";
			break;
		case INSERT_OBJECT_REFERENCE:
			selectQuery = "INSERT INTO scms_objects_referencer " +
						  "(object_id, object_type, object_child_id, object_child_type, object_order) " +
						  "VALUES (?, ?, ?, ?, ?)";
			break;
		case DELETE_OBJECT_FROM_REFERENCE:
			selectQuery = "DELETE FROM sobcms.scms_objects_referencer "+ 
						  "WHERE object_id = ? AND object_type = ? "+ 
						  "AND object_child_id = ? AND object_child_type = ?";
			break;
		}
		return selectQuery;
	}
	
	public synchronized void insertNewMenu(Object parantObject, Menus menu,int order){
		PreparedStatement pstmt = null;
		try {			
			pstmt = conn.prepareStatement(getSqlQuery(INSERT_MENU_ITEM),Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, menu.getMenuItem(Menus.MENU_NAME));
			pstmt.setInt(2, Integer.parseInt(menu.getMenuItem(Menus.MENU_TYPE_ID)));
			pstmt.setDate(3, new java.sql.Date(DateUtils.df.parse(menu.getMenuItem(Menus.PUBLICHED_FROM)).getTime()));
			pstmt.setDate(4, null);
			pstmt.setString(5, "0");
			pstmt.executeUpdate();
			
			int insertedMenuId = -1;
			ResultSet rs = pstmt.getGeneratedKeys();
			if(rs.next()){
				insertedMenuId = rs.getInt(1);
			}
			if(insertedMenuId == -1)
				throw new Exception("Inser ID Error");
			
			pstmt = conn.prepareStatement(getSqlQuery(INSERT_OBJECT_REFERENCE));
			pstmt.setInt(1,((BaseDataObject)parantObject).getObjectId());
			pstmt.setString(2,((BaseDataObject)parantObject).getObjectType());
			pstmt.setInt(3,insertedMenuId);
			pstmt.setString(4,menu.getObjectType());
			pstmt.setInt(5,order);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		
	}
	
	public synchronized void updateObject(Object obj){
		
		if(((BaseDataObject)obj).getObjectType().equals("menu")){
			updateMenu((Menus)obj);
		}
		
		
	}
	
	
	public void updateMenu(Menus menu){
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(getSqlQuery(UPDATE_MENU_ITEM));
			pstmt.setString(1, menu.getMenuItem(Menus.MENU_NAME));
			pstmt.setInt(2, Integer.parseInt(menu.getMenuItem(Menus.MENU_TYPE_ID)));
			java.sql.Timestamp pubFrom = new java.sql.Timestamp(DateUtils.df.parse(DateUtils.getDateTime()).getTime());
			if(menu.getMenuItem(Menus.PUBLICHED_FROM) != null)
				pubFrom = new java.sql.Timestamp(DateUtils.df.parse(menu.getMenuItem(Menus.PUBLICHED_FROM)).getTime());
			pstmt.setTimestamp(3, pubFrom);
			java.sql.Timestamp pubTo = null;
			if(menu.getMenuItem(Menus.PUBLICHED_TO) != null)
				pubTo = new java.sql.Timestamp(DateUtils.df.parse(menu.getMenuItem(Menus.PUBLICHED_TO)).getTime());
			pstmt.setTimestamp(4, pubTo);
			pstmt.setInt(5, Integer.parseInt(menu.getMenuItem(Menus.MENUS_ID)));
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	public synchronized void reorderItem(DefaultMutableTreeNode ParentNode){
		BaseDataObject baseObjectParent = (BaseDataObject)ParentNode.getUserObject();
		HashMap <Integer, Object[]> objectsToAddOrRemoveOnSave = baseObjectParent.getObjectsToAddOrRemoveOnSave();
		for(int i=0; i<objectsToAddOrRemoveOnSave.size();i++){
			BaseDataObject obj = (BaseDataObject)((objectsToAddOrRemoveOnSave.get(i+1))[1]);
			if((Boolean)((objectsToAddOrRemoveOnSave.get(i+1))[0])){
			  insertItemReference(baseObjectParent.getObjectId(),baseObjectParent.getObjectType()
					, obj.getObjectId()
					, obj.getObjectType()
					, obj.getObjectOrder());
			}else{
			  deleteItemReference(baseObjectParent.getObjectId(),baseObjectParent.getObjectType()
					, obj.getObjectId()
					, obj.getObjectType());
			}
		}
		Enumeration parentNodeEnum = ParentNode.children();
		while(parentNodeEnum.hasMoreElements()){
			DefaultMutableTreeNode child = (DefaultMutableTreeNode)parentNodeEnum.nextElement();
			BaseDataObject baseObjectChild = (BaseDataObject)child.getUserObject();
			updateItemOrder(baseObjectParent.getObjectId(),baseObjectParent.getObjectType(),
					baseObjectChild.getObjectId(),baseObjectChild.getObjectType(), ParentNode.getIndex(child));
		}
		baseObjectParent.clearObjectsOnSave();
	}
	
	private void insertItemReference(int parantObjectId, String parantObjectType,
			int childObjectId,String childObjectType, int order){
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(getSqlQuery(INSERT_OBJECT_REFERENCE));
			pstmt.setInt(1,parantObjectId);
			pstmt.setString(2,parantObjectType);
			pstmt.setInt(3,childObjectId);
			pstmt.setString(4,childObjectType);
			pstmt.setInt(5,order);
			pstmt.executeUpdate();	
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void deleteItemReference(int parantObjectId, String parantObjectType,
			int childObjectId,String childObjectType){
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(getSqlQuery(DELETE_OBJECT_FROM_REFERENCE));
			pstmt.setInt(1,parantObjectId);
			pstmt.setString(2,parantObjectType);
			pstmt.setInt(3,childObjectId);
			pstmt.setString(4,childObjectType);
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private int getChildCountFromDb(int id, String type){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int childCount = -1;
		try {
			pstmt = conn.prepareStatement(getSqlQuery(UPDATE_ITEM_ORDER));
			pstmt.setInt(1, id);
			pstmt.setString(2, type);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				childCount = rs.getInt("items_count");
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return childCount;
	}
	
	private void updateItemOrder(int parantObjectId, String parantObjectType,
			int childObjectId,String childObjectType, int order){
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(getSqlQuery(UPDATE_ITEM_ORDER));
			pstmt.setInt(1,order);
			pstmt.setInt(2,parantObjectId);
			pstmt.setString(3,parantObjectType);
			pstmt.setInt(4,childObjectId);
			pstmt.setString(5,childObjectType);
			pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void soapWebServise(){
		try {
            SOAPConnectionFactory soapConnectionFactory =
                SOAPConnectionFactory.newInstance();
            SOAPConnection connection =
                soapConnectionFactory.createConnection();

            MessageFactory factory = MessageFactory.newInstance();
            SOAPMessage message = factory.createMessage();

            SOAPHeader header = message.getSOAPHeader();
            SOAPBody body = message.getSOAPBody();
            header.detachNode();

            QName bodyName = new QName("https://localhost",
                "hello_world", "nu");
            SOAPBodyElement bodyElement = body.addBodyElement(bodyName);

            QName name = new QName("soapVal");
            SOAPElement symbol = bodyElement.addChildElement(name);
            symbol.addTextNode("Testik");
            message.writeTo(System.out);
            URL endpoint = new URL("http://localhost/jcms/soap/jcms.php");
            SOAPMessage response = connection.call(message, endpoint);
            response.writeTo(System.out);
            connection.close();

            SOAPBody soapBody = response.getSOAPBody();
            
            Iterator iterator = soapBody.getChildElements(bodyName);
            bodyElement = (SOAPBodyElement)iterator.next();
            String lastPrice = bodyElement.getValue();

            System.out.print("The last price for SUNW is ");
            System.out.println(lastPrice);

        } catch (Exception ex) {
            ex.printStackTrace();
        }

	}
	
}



